package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.bo.DepositWithdrawBO;
import com.kcs.fxmargin.model.TAccOnline;

public class AccOnlineDao {
	
	public List<DepositWithdrawBO> searchDepositWithdraw(Connection conn, PreparedStatement pstm, ResultSet rs, DepositWithdrawBO bo) throws Exception{
		
		List<DepositWithdrawBO> boList 		= new ArrayList<DepositWithdrawBO>();
		StringBuffer sql 					= new StringBuffer();
		DepositWithdrawBO depositWithDrawBo = null;
		
		sql.append(" select c.cif_no, c.margin_acc_no, c.cust_name, c.margin_acc_name, a.as_of_date date,  ");
		sql.append(" case a.type when 'D' then 'Deposit' when 'W' then 'Withdraw' end type_name, a.type type_id, ");
		sql.append(" a.amount, a.ref_no, u.user_name update_by, u.update_dt, a.id, a.row_version  ");
		sql.append(" from m_cust_info c, t_acc_online a left join m_users u on a.update_by = u.user_id ");
		sql.append(" where a.cust_info_id=c.id  ");
		sql.append(" and c.active_status='A' ");
		sql.append(" and a.active_status='A' ");
		
		if(bo.getCifNo() != null && !"".equals(bo.getCifNo()))  					sql.append(" and c.cif_no = ? ");
		if(bo.getCustomerName() != null && !"".equals(bo.getCustomerName()))  		sql.append(" and UPPER(c.cust_name) like UPPER(?) ");
		if(bo.getMarginAccNo() != null && !"".equals(bo.getMarginAccNo()))  		sql.append(" and c.margin_acc_no = ? ");
		if(bo.getMarginAccName() != null && !"".equals(bo.getMarginAccName()))  	sql.append(" and UPPER(c.margin_acc_name) like UPPER(?) ");
		if(bo.getTransactionDate() != null && !"".equals(bo.getTransactionDate()))  sql.append(" and CONVERT(varchar(10), a.as_of_date, 103) = ? ");
		
		System.out.println("SQL -> " + sql.toString());
		int index = 1;
		
		pstm = conn.prepareStatement(sql.toString());
		
		if(bo.getCifNo() != null && !"".equals(bo.getCifNo())){
			pstm.setString(index, bo.getCifNo());
			index++;
		}
		
		if(bo.getCustomerName() != null && !"".equals(bo.getCustomerName())){
			pstm.setString(index, bo.getCustomerName());
			index++;
		}
		
		if(bo.getMarginAccNo() != null && !"".equals(bo.getMarginAccNo())){
			pstm.setString(index, bo.getMarginAccNo());
			index++;
		}
		
		if(bo.getMarginAccName() != null && !"".equals(bo.getMarginAccName())){
			pstm.setString(index, bo.getMarginAccName());
			index++;
		}
		
		if(bo.getTransactionDate() != null && !"".equals(bo.getTransactionDate())){
			pstm.setString(index, bo.getTransactionDate());
			index++;
		}
		
		rs = pstm.executeQuery();
		
		index = 1;
		while(rs.next()){
			depositWithDrawBo = new DepositWithdrawBO();
			depositWithDrawBo.setRowNum(index);
			depositWithDrawBo.setId(rs.getString("id"));
			depositWithDrawBo.setCifNo(rs.getString("cif_no"));
			depositWithDrawBo.setCustomerName(rs.getString("cust_name"));
			depositWithDrawBo.setMarginAccNo(rs.getString("margin_acc_no"));
			depositWithDrawBo.setMarginAccName(rs.getString("margin_acc_name"));
			depositWithDrawBo.setTransactionDate(commonUtils.parseStringDateByFormat(rs.getString("date"),"yyyy-MM-dd","dd/MM/yyyy"));
			depositWithDrawBo.setDepositWithdraw(rs.getString("type_name"));
			depositWithDrawBo.setDepositWithdrawId(rs.getString("type_id"));
			depositWithDrawBo.setAmount(rs.getBigDecimal("amount"));
			depositWithDrawBo.setReferanceNo(rs.getString("ref_no"));
			depositWithDrawBo.setUpdateBy(rs.getString("update_by"));
			depositWithDrawBo.setUpdateDate(commonUtils.parseStringDateByFormat(rs.getString("update_dt"),"yyyy-MM-dd hh:mm:ss","dd/MM/yyyy"));
			depositWithDrawBo.setRowVersion(rs.getInt("row_version"));
			depositWithDrawBo.setJsonObject();
			
			
			boList.add(depositWithDrawBo);
			index++;
		}
		
		System.out.println("boList = " + boList.size());
		
		return boList;
	}
	
	public String insert(Connection conn, PreparedStatement pstm, TAccOnline accOnline) throws Exception{
		
		StringBuffer sql 		= new StringBuffer();
		
		int insert = 0;
		
		sql.append(" insert into t_acc_online ");
		sql.append(" (id, cust_info_id, type, amount, ref_no, as_of_date, value_date, active_status, create_by, create_dt, update_by, update_dt, row_version) ");
		sql.append(" values (?,?,?,?,?,?,?,?,?,getDate(),?,getDate(),?) ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, accOnline.getId());
		pstm.setString(2, accOnline.getCustInfoId());
		pstm.setString(3, accOnline.getType());
		pstm.setBigDecimal(4, accOnline.getAmount());
		pstm.setString(5, accOnline.getRefNo());
		pstm.setDate(6, commonUtils.convertUtilDatetoSQLDate(accOnline.getAsOfDate()));
		pstm.setDate(7, commonUtils.convertUtilDatetoSQLDate(accOnline.getValueDate()));
		pstm.setString(8, accOnline.getActiveStatus());
		pstm.setString(9, accOnline.getCreateby());
		pstm.setString(10, accOnline.getUpdateBy());
		pstm.setInt(11, 1);
		
		insert = pstm.executeUpdate();
		
		System.out.println("insert = " + insert);
		
		return insert == 1 ? "s" : "e";
		
	}
	
	public String update(Connection conn, PreparedStatement pstm, TAccOnline accOnline) throws Exception{
		
		StringBuffer sql 		= new StringBuffer();
		
		int update = 0;
		
		sql.append(" update t_acc_online set  ");
		sql.append(" value_date = ? ,");
		sql.append(" type = ? ,  ");
		sql.append(" amount = ? ,  ");
		sql.append(" ref_no = ? , ");
		sql.append(" update_by = ? , ");
		sql.append(" update_dt = getDate() , ");
		sql.append(" row_version = ? ");
		sql.append(" where id = ? ");

		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setDate(1, commonUtils.convertUtilDatetoSQLDate(accOnline.getValueDate()));
		pstm.setString(2, accOnline.getType());
		pstm.setBigDecimal(3, accOnline.getAmount());
		pstm.setString(4, accOnline.getRefNo());
		pstm.setString(5, accOnline.getUpdateBy());
		pstm.setInt(6, accOnline.getRowVersion());
		pstm.setString(7, accOnline.getId());
		
		update = pstm.executeUpdate();
		
		System.out.println("update = " + update);
		
		return update == 1 ? "s" : "e";
	}
	
	public String delete(Connection conn, PreparedStatement pstm, String id) throws Exception{
		
		StringBuffer sql 		= new StringBuffer();
		
		int delete = 0;

		sql.append(" delete from t_acc_online where id = ? ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, id);
		
		delete = pstm.executeUpdate();
		
		System.out.println("delete = " + delete);
		
		return delete == 1 ? "s" : "e";
	}
}
